package com.scoresystem.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import com.scoresystem.common.CommonValue;
import com.scoresystem.dao.IStudentDao;
import com.scoresystem.exception.UserRepeatException;
import com.scoresystem.model.StuScore;
import com.scoresystem.model.Student;
import com.scoresystem.model.Subject;
import com.scoresystem.model.User;
import com.scoresystem.utils.ResultMapUtil;

public class StudentDaoImpl extends BaseDaoImpl implements IStudentDao {

	public StudentDaoImpl(Connection conn) {
		super(conn);
		this.conn=conn;
	}
	/**
	 * 对查找的重复操作进行封装
	 * @return
	 */
	private Student find(String sql){
		try{
			ResultSet rs=this.templateQuery(sql);
			Student student=new Student();
			if(rs.next()){
				Map<String,String> map=ResultMapUtil.getResultMap(rs);
				try{
					BeanUtils.populate(student, map);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			//如果还有记录，说明名字有重复，向上抛出异常
			if(rs.next())
				throw new UserRepeatException("you have a repeated user");
			else
				return student;
				
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public boolean add(Student student) {
		String sql="insert into "+CommonValue.TAB_STUDENT+" (stunum,stuname"
				+ ",major,stuclass,password)"+"values"+"("+
				"'"+student.getStunum()+"'"+
				","+"'"+student.getStuname()+"'"+","
				+"'"+student.getMajor()+"'"+","
				+"'"+student.getStuclass()+"'"+","+"'"+student.getPassword()+"'"+")";
		try{
			this.templateUpdate(sql);
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean modify(Student student) {
		String sql="update student set stunum="+"'"+student.getStunum()+"'"+","
				+ " stuname="+"'"+student.getStuname()+"'"+","
				+" major="+"'"+student.getMajor()+"'"+","
				+" stuclass="+"'"+student.getStuclass()+"'"+","
				+" password="+"'"+student.getPassword()+"'"+
				" where stunum="+"'"+student.getStunum()+"'";
		try{
			this.templateUpdate(sql);
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean deleteStudents(String[] stunum) {
		try{
			for(String stunum1:stunum){
				String sql="delete from "+CommonValue.TAB_STUDENT+" where stunum="
						+"'"+stunum1+"'";
				this.templateUpdate(sql);
			}
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public Student findbyName(String stuname) {
		String sql="select * from "+CommonValue.TAB_STUDENT+" where stuname="+"'"+stuname+"'";
		return find(sql);
	}

	@Override
	public Student findbyNum(String stunum) {
		String sql="select * from "+CommonValue.TAB_STUDENT+" where stunum="+"'"+stunum+"'";
		return find(sql);
	}
	@Override
	public List<String> getClasses() {
		String sql="select distinct stuclass from "+CommonValue.TAB_STUDENT;
		List<String> list=new ArrayList<String>();
		try{
			ResultSet rs=this.templateQuery(sql);
			while(rs.next()){
				String name=rs.getString(1);
				list.add(name);
			}
			return list;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<String> getMajors() {
		String sql="select distinct major from "+CommonValue.TAB_STUDENT;
		List<String> list=new ArrayList<String>();
		try{
			ResultSet rs=this.templateQuery(sql);
			while(rs.next()){
				String name=rs.getString(1);
				list.add(name);
			}
			return list;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<Student> listbyMajorandClass(String major, String stuclass) {
		String sql="select * from student where major="+"'"+major+"'"+" and "+
	"stuclass="+"'"+stuclass+"'";
		List<Student> list=new ArrayList<Student>();
		try {
			ResultSet rs=templateQuery(sql);
			while(rs.next()){
				Map<String,String> map=ResultMapUtil.getResultMap(rs);				
				Student student=new Student();
				try {
					BeanUtils.populate(student, map);
				} catch (Exception e) {
					e.printStackTrace();
				} 
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}  
        
		return list;
	}
	@Override
	public List<StuScore> listbyClass(String stuclass) {
		String sql="select stunum,stuname from student where stuclass="+
	"'"+stuclass+"'";
		List<StuScore> list=new ArrayList<StuScore>();
		try{
			ResultSet rs=templateQuery(sql);
			while(rs.next()){
				String stunum=rs.getString(1);
				String stuname=rs.getString(2);
				Integer score=0;
				StuScore ss=new StuScore();
				ss.setStunum(stunum);ss.setStuname(stuname);ss.setScore(score);
				list.add(ss);
			}
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}

}
